package com.sailing.xjpb.orm.utils;

import com.google.common.collect.Maps;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SetOperationList;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.update.UpdateSet;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.StringReader;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 多租户sql解析设置
 *
 * @author FanHaiChao
 * createDate 2024/1/25 10:58
 */
public class SailingTenantSqlParser {

    private SailingTenantSqlParser() {
        throw new IllegalStateException("Utility class");
    }

    /**
     * 插入方法
     */
    private static final String INSERT_METHOD = "insert ";

    /**
     * 更新方法
     */
    private static final String UPDATE_METHOD = "update ";

    /**
     * 删除方法
     */
    private static final String DELETE_METHOD = "delete ";

    /**
     * 查询方法
     */
    private static final String SELECT_METHOD = "select ";

    /**
     * 租户code字段
     */
    private static final String TENANT_CODE = "tenant_code";

    /**
     * 错误信息
     */
    private static final String ERROR_MSG = "租户权限sql解析出错，sql:{}";

    /**
     * 正则匹配模式map
     */
    private static final Map<String, Pattern> PATTERN_MAP = Maps.newHashMap();

    /**
     * 日志
     */
    private static final Logger log = LoggerFactory.getLogger(SailingTenantSqlParser.class);


    public static String getTenantSql(String sql, String tenantCode) {
        String lowerSql = sql.toLowerCase(Locale.ROOT);
        //去除sql中的空格，用于比较tenant_code=这个字符串
        String compactSql = lowerSql.replace(" ", "");
        //是否是插入语句
        boolean isInsertSql = sqlMatches(INSERT_METHOD, lowerSql);
        if ((!isInsertSql && compactSql.contains(TENANT_CODE + "=")) || (isInsertSql && lowerSql.contains(TENANT_CODE))) {
            if (log.isDebugEnabled()) {
                log.warn("sql中已经包含了租户编码，无需重复设置，sql：{}", lowerSql);
            }
            return sql;
        }
        try {
            if (StringUtils.isBlank(tenantCode)) {
                if (log.isDebugEnabled()) {
                    log.warn("租户code为空，sql：{}", lowerSql);
                }
                return sql;
            }
            CCJSqlParserManager pm = new CCJSqlParserManager();
            if (isInsertSql) {
                return getInsertSql(tenantCode, pm, sql);
            } else if (sqlMatches(UPDATE_METHOD, lowerSql)) {
                return getUpdateSql(tenantCode, pm, sql);
            } else if (sqlMatches(DELETE_METHOD, lowerSql)) {
                return getDeleteSql(tenantCode, pm, sql);
            } else if (sqlMatches(SELECT_METHOD, lowerSql)) {
                return getSelectSql(tenantCode, pm, sql);
            } else {
                return sql;
            }
        } catch (Exception e) {
            log.error(ERROR_MSG, lowerSql, e);
            return lowerSql;
        }
    }

    /**
     * 使用正则表达式匹配sql，返回是否匹配成功
     *
     * @param regex    正则表达式
     * @param lowerSql 匹配的sql
     * @return 是否匹配成功
     */
    private static boolean sqlMatches(String regex, String lowerSql) {
        // 编译正则表达式（不区分大小写）
        Pattern pattern = PATTERN_MAP.computeIfAbsent(regex, key -> Pattern.compile(regex, Pattern.CASE_INSENSITIVE));
        // 创建匹配器对象，并匹配输入字符串
        Matcher matcher = pattern.matcher(lowerSql);
        // 使用find方法检查是否存在匹配项
        return matcher.find();
    }

    /**
     * 获取插入的sql
     *
     * @param tenantCode 租户code
     * @param pm         jpasql解析管理器
     * @param sql        转换的sql
     * @return 转换后的sql
     */
    private static String getInsertSql(String tenantCode, CCJSqlParserManager pm, String sql) throws JSQLParserException {
        Insert insert = (Insert) pm.parse(new StringReader(sql));
        //添加租户编码字段
        insert.addColumns(new Column(TENANT_CODE));
        ExpressionList itemsList = (ExpressionList) insert.getItemsList();
        //添加租户编码的值
        itemsList.addExpressions(new StringValue(tenantCode));
        return insert.toString();
    }

    /**
     * 获取更新的sql
     *
     * @param tenantCode 租户code
     * @param pm         jpasql解析管理器
     * @param sql        转换的sql
     * @return 转换后的sql
     */
    private static String getUpdateSql(String tenantCode, CCJSqlParserManager pm, String sql) throws JSQLParserException {
        Update update = (Update) pm.parse(new StringReader(sql));
        //去除set中的租户编码字段
        removeTenantSet(update);
        //获取租户编码的where条件
        Expression where = update.getWhere();
        Expression tenantWhere = getTenantWhere(where, tenantCode);
        update.setWhere(tenantWhere);
        return update.toString();
    }

    /**
     * 获取删除的sql
     *
     * @param tenantCode 租户code
     * @param pm         jpasql解析管理器
     * @param sql        转换的sql
     * @return 转换后的sql
     */
    private static String getDeleteSql(String tenantCode, CCJSqlParserManager pm, String sql) throws JSQLParserException {
        Delete delete = (Delete) pm.parse(new StringReader(sql));
        Expression where = delete.getWhere();
        //获取租户编码的where条件
        Expression tenantWhere = getTenantWhere(where, tenantCode);
        delete.setWhere(tenantWhere);
        return delete.toString();
    }

    /**
     * 获取查询的sql
     *
     * @param tenantCode 租户code
     * @param pm         jpasql解析管理器
     * @param sql        转换的sql
     * @return 转换后的sql
     */
    private static String getSelectSql(String tenantCode, CCJSqlParserManager pm, String sql) throws JSQLParserException {
        Select select = (Select) pm.parse(new StringReader(sql));
        SelectBody selectBody = select.getSelectBody();
        if (!sql.contains("union") || !sql.contains("UNION")) {
            //如果sql没有union，直接拼接where
            PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
            Expression where = plainSelect.getWhere();
            //获取租户编码的where条件
            Expression tenantWhere = getTenantWhere(where, tenantCode);
            plainSelect.setWhere(tenantWhere);
        } else {
            //如果sql中有union，需要拿到单独的sql语句，遍历拼接where
            SetOperationList setOperationList = (SetOperationList) selectBody;
            List<SelectBody> selects = setOperationList.getSelects();
            for (SelectBody unionSelectBody : selects) {
                PlainSelect plainSelect = (PlainSelect) unionSelectBody;
                Expression where = plainSelect.getWhere();
                //获取租户编码的where条件
                Expression tenantWhere = getTenantWhere(where, tenantCode);
                plainSelect.setWhere(tenantWhere);
            }
        }
        return selectBody.toString();
    }

    /**
     * 获取多租户的where条件
     *
     * @param where      原where条件
     * @param tenantCode 租户code
     * @return 新的where条件
     */
    private static Expression getTenantWhere(Expression where, String tenantCode) {
        //创建租户条件
        EqualsTo tenantWhere = new EqualsTo();
        tenantWhere.setLeftExpression(new Column(TENANT_CODE));
        tenantWhere.setRightExpression(new StringValue("'" + tenantCode + "'"));
        if (Objects.nonNull(where)) {
            //如果原来的where条件不为空，则在之前加上租户条件，租户code有索引
            AndExpression andExpression = new AndExpression();
            andExpression.setLeftExpression(tenantWhere);
            andExpression.setRightExpression(where);
            return andExpression;
        } else {
            //否则直接返回租户条件
            return tenantWhere;
        }
    }

    /**
     * 去除update语句中set的租户编码字段
     *
     * @param update update解析语句
     */
    private static void removeTenantSet(Update update) {
        ArrayList<UpdateSet> updateSets = update.getUpdateSets();
        updateSets.removeIf(updateSet -> {
            List<Column> columns = updateSet.getColumns();
            return columns.stream().anyMatch(column -> column.getColumnName().equals(TENANT_CODE));
        });
    }

}
